*open 1990 raw-raw is a copy from excel and paste into stata*

forvalues i = 1990/2006 {
	use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\a`i'rawnew.dta"
	drop if miscellaneous=="Feb-2007"
	drop if miscellaneous==""
	drop if weeks==""
	renvars, postf(_`i')
	rename marketid_`i' marketid
	save rating`i'finalnew.dta, replace
}
use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\rating1990finalnew.dta"

joinby marketid using rating1991finalnew.dta, unmatched(both)
tab _merge
rename _merge merge1

joinby marketid using rating1992finalnew.dta, unmatched(both)
tab _merge
rename _merge merge2

joinby marketid using rating1993finalnew.dta, unmatched(both)
tab _merge
rename _merge merge3

joinby marketid using rating1994finalnew.dta, unmatched(both)
tab _merge
rename _merge merge4

joinby marketid using rating1995finalnew.dta, unmatched(both)
tab _merge
rename _merge merge5

joinby marketid using rating1996finalnew.dta, unmatched(both)
tab _merge
rename _merge merge6

joinby marketid using rating1997finalnew.dta, unmatched(both)
tab _merge
rename _merge merge7

joinby marketid using rating1998finalnew.dta, unmatched(both)
tab _merge
rename _merge merge8

joinby marketid using rating1999finalnew.dta, unmatched(both)
tab _merge
rename _merge merge9

joinby marketid using rating2000finalnew.dta, unmatched(both)
tab _merge
rename _merge merge10

joinby marketid using rating2001finalnew.dta, unmatched(both)
tab _merge
rename _merge merge11

joinby marketid using rating2002finalnew.dta, unmatched(both)
tab _merge
rename _merge merge12

joinby marketid using rating2003finalnew.dta, unmatched(both)
tab _merge
rename _merge merge13

joinby marketid using rating2004finalnew.dta, unmatched(both)
tab _merge
rename _merge merge14

joinby marketid using rating2005finalnew.dta, unmatched(both)
tab _merge
rename _merge merge15

joinby marketid using rating2006finalnew.dta, unmatched(both)
tab _merge
rename _merge merge16

renvars, presub (demo10 ra_w12_17)
renvars, presub (demo11 ra_w18_24)
renvars, presub (demo12 ra_w25_34)
renvars, presub (demo13 ra_w35_49)
renvars, presub (demo14 ra_w50_54)
renvars, presub (demo15 ra_w55_64)
renvars, presub (demo16 ra_w65plus)
renvars, presub (demo1 ra_c2_5)
renvars, presub (demo2 ra_c6_11)
renvars, presub (demo3 ra_m12_17)
renvars, presub (demo4 ra_m18_24)
renvars, presub (demo5 ra_m25_34)
renvars, presub (demo6 ra_m35_49)
renvars, presub (demo7 ra_m50_54)
renvars, presub (demo8 ra_m55_64)
renvars, presub (demo9 ra_m65plus)
keep marketid market_1990 market_2000 market_2006 ra_c2_5_* ra_c6_11_* ra_m12_17_* ra_m18_24_* ra_m25_34_* ra_m35_49_* ra_m50_54_* ra_m55_64_* ra_m65plus_* ra_w12_17_* ra_w18_24_* ra_w25_34_* ra_w35_49_* ra_w50_54_* ra_w55_64_* ra_w65plus_* 
order marketid market_1990 market_2000 market_2006

save rating19902006finalnew.dta, replace

*Universe- The age groups in the Universe data are different for different years. The order of the variables in raw data varies by year-The units also vary by year-The names of the dma vary slightly* 
*The format of the universe raw data almost coincides for years 1992, 2000, 2002, 2003, 2004, 2005, and 2006 (eliminate comma in 2006 eliminate word "Total" from dmacode in 2002-2003)*
*The format of the raw data coincides for years 1991, 1998, 1999, and 2001*
*The format of the raw data coincides for years 1993, 1994, 1995, and 1997*
*1990 and 1996 are unique*
clear

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw90.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var29, replace
rename var1 dmacode
rename var3 totalhh_1990
rename var4 tvhh_1990 
rename var5 un_c2_5_1990
rename var6 un_c6_11_1990
gen un_m12_17_1990=var7+var8
rename  var9 un_m18_24_1990
rename  var10 un_m25_34_1990
gen  un_m35_49_1990=var11+var12
rename var13 un_m50_54_1990
rename var14 un_m55_64_1990
rename var15 un_m65plus_1990
gen un_w12_17_1990=var16+var17
rename  var18 un_w18_24_1990
rename  var19 un_w25_34_1990
gen  un_w35_49_1990=var20+var21
rename var22 un_w50_54_1990
rename var23 un_w55_64_1990
rename var24 un_w65plus_1990
rename var25 workingwomen_1990
rename var26 black_1990
rename var28 spanish_1990
rename var29 hhnoadults_1990
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1990 tvhh_1990 un_c2_5_1990 un_c6_11_1990 un_m12_17_1990 un_m18_24_1990 un_m25_34_1990 un_m35_49_1990 un_m50_54_1990 un_m55_64_1990 un_m65plus_1990 un_w12_17_1990 un_w18_24_1990 un_w25_34_1990 un_w35_49_1990 un_w50_54_1990 un_w55_64_1990 un_w65plus_1990 workingwomen_1990 black_1990 spanish_1990 hhnoadults_1990
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe90.dta", replace

clear
use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw96.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var36, replace
rename var1 dmacode
rename var3 totalhh_1996
rename var4 tvhh_1996 
rename var6 un_c2_5_1996
rename var7 un_c6_11_1996
gen un_m12_17_1996=var8+var9
gen un_m18_24_1996=var10+var11
rename  var12 un_m25_34_1996
gen  un_m35_49_1996=var13+var14
rename var15 un_m50_54_1996
rename var16 un_m55_64_1996
rename var17 un_m65plus_1996
gen un_w12_17_1996=var18+var19
gen un_w18_24_1996=var20+var21
rename  var22 un_w25_34_1996
gen  un_w35_49_1996=var23+var24
rename var25 un_w50_54_1996
rename var26 un_w55_64_1996
rename var27 un_w65plus_1996
rename var28 workingwomen_1996
rename var33 black_1996
rename var35 spanish_1996
rename var36 hhnoadults_1996
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1996 tvhh_1996 un_c2_5_1996 un_c6_11_1996 un_m12_17_1996 un_m18_24_1996 un_m25_34_1996 un_m35_49_1996 un_m50_54_1996 un_m55_64_1996 un_m65plus_1996 un_w12_17_1996 un_w18_24_1996 un_w25_34_1996 un_w35_49_1996 un_w50_54_1996 un_w55_64_1996 un_w65plus_1996 workingwomen_1996 black_1996 spanish_1996 hhnoadults_1996
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe96.dta", replace


clear
*1991-1998-1999, and 2001*

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw91.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var31, replace
rename var1 dmacode
rename var3 totalhh_1991
rename var4 tvhh_1991 
rename var5 un_c2_5_1991
rename var6 un_c6_11_1991
gen un_m12_17_1991=var7+var8
gen un_m18_24_1991=var9+var10
rename  var11 un_m25_34_1991
gen  un_m35_49_1991=var12+var13
rename var14 un_m50_54_1991
rename var15 un_m55_64_1991
rename var16 un_m65plus_1991
gen un_w12_17_1991=var17+var18
gen un_w18_24_1991=var19+var20
rename  var21 un_w25_34_1991
gen  un_w35_49_1991=var22+var23
rename var24 un_w50_54_1991
rename var25 un_w55_64_1991
rename var26 un_w65plus_1991
rename var27 workingwomen_1991
rename var28 black_1991
rename var30 spanish_1991
rename var31 hhnoadults_1991
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1991 tvhh_1991 un_c2_5_1991 un_c6_11_1991 un_m12_17_1991 un_m18_24_1991 un_m25_34_1991 un_m35_49_1991 un_m50_54_1991 un_m55_64_1991 un_m65plus_1991 un_w12_17_1991 un_w18_24_1991 un_w25_34_1991 un_w35_49_1991 un_w50_54_1991 un_w55_64_1991 un_w65plus_1991 workingwomen_1991 black_1991 spanish_1991 hhnoadults_1991
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe91.dta", replace

clear


use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw98.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var31, replace
rename var1 dmacode
rename var3 totalhh_1998
rename var4 tvhh_1998 
rename var5 un_c2_5_1998
rename var6 un_c6_11_1998
gen un_m12_17_1998=var7+var8
gen un_m18_24_1998=var9+var10
rename  var11 un_m25_34_1998
gen  un_m35_49_1998=var12+var13
rename var14 un_m50_54_1998
rename var15 un_m55_64_1998
rename var16 un_m65plus_1998
gen un_w12_17_1998=var17+var18
gen un_w18_24_1998=var19+var20
rename  var21 un_w25_34_1998
gen  un_w35_49_1998=var22+var23
rename var24 un_w50_54_1998
rename var25 un_w55_64_1998
rename var26 un_w65plus_1998
rename var27 workingwomen_1998
rename var28 black_1998
rename var30 spanish_1998
rename var31 hhnoadults_1998
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1998 tvhh_1998 un_c2_5_1998 un_c6_11_1998 un_m12_17_1998 un_m18_24_1998 un_m25_34_1998 un_m35_49 un_m50_54_1998 un_m55_64_1998 un_m65plus_1998 un_w12_17_1998 un_w18_24_1998 un_w25_34_1998 un_w35_49_1998 un_w50_54_1998 un_w55_64_1998 un_w65plus_1998 workingwomen_1998 black_1998 spanish_1998 hhnoadults_1998
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe98.dta", replace

clear


use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw99.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var31, replace
rename var1 dmacode
rename var3 totalhh_1999
rename var4 tvhh_1999 
rename var5 un_c2_5_1999
rename var6 un_c6_11_1999
gen un_m12_17_1999=var7+var8
gen un_m18_24_1999=var9+var10
rename  var11 un_m25_34_1999
gen  un_m35_49_1999=var12+var13
rename var14 un_m50_54_1999
rename var15 un_m55_64_1999
rename var16 un_m65plus_1999
gen un_w12_17_1999=var17+var18
gen un_w18_24_1999=var19+var20
rename  var21 un_w25_34_1999
gen  un_w35_49_1999=var22+var23
rename var24 un_w50_54_1999
rename var25 un_w55_64_1999
rename var26 un_w65plus_1999
rename var27 workingwomen_1999
rename var28 black_1999
rename var30 spanish_1999
rename var31 hhnoadults_1999
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1999 tvhh_1999 un_c2_5_1999 un_c6_11_1999 un_m12_17_1999 un_m18_24_1999 un_m25_34_1999 un_m35_49_1999 un_m50_54_1999 un_m55_64_1999 un_m65plus_1999 un_w12_17_1999 un_w18_24_1999 un_w25_34_1999 un_w35_49_1999 un_w50_54_1999 un_w55_64_1999 un_w65plus_1999 workingwomen_1999 black_1999 spanish_1999 hhnoadults_1999
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe99.dta", replace

clear
use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw01.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var31, replace
rename var1 dmacode
rename var3 totalhh_2001
rename var4 tvhh_2001 
rename var5 un_c2_5_2001
rename var6 un_c6_11_2001
gen un_m12_17_2001=var7+var8
gen un_m18_24_2001=var9+var10
rename  var11 un_m25_34_2001
gen  un_m35_49_2001=var12+var13
rename var14 un_m50_54_2001
rename var15 un_m55_64_2001
rename var16 un_m65plus_2001
gen un_w12_17_2001=var17+var18
gen un_w18_24_2001=var19+var20
rename  var21 un_w25_34_2001
gen  un_w35_49_2001=var22+var23
rename var24 un_w50_54_2001
rename var25 un_w55_64_2001
rename var26 un_w65plus_2001
rename var27 workingwomen_2001
rename var28 black_2001
rename var30 spanish_2001
rename var31 hhnoadults_2001
gen dmaname=upper(var2)
drop if dmacode==.
keep dmaname dmacode totalhh_2001 tvhh_2001 un_c2_5_2001 un_c6_11_2001 un_m12_17_2001 un_m18_24_2001 un_m25_34_2001 un_m35_49_2001 un_m50_54_2001 un_m55_64_2001 un_m65plus_2001 un_w12_17_2001 un_w18_24_2001 un_w25_34_2001 un_w35_49_2001 un_w50_54_2001 un_w55_64_2001 un_w65plus_2001 workingwomen_2001 black_2001 spanish_2001 hhnoadults_2001
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe01.dta", replace

clear


*1993-1994-1995-1997*

clear
use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw93.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var32, replace
rename var1 dmacode
rename var3 totalhh_1993
rename var4 tvhh_1993 
rename var6 un_c2_5_1993
rename var7 un_c6_11_1993
gen un_m12_17_1993=var8+var9
gen un_m18_24_1993=var10+var11
rename  var12 un_m25_34_1993
gen  un_m35_49_1993=var13+var14
rename var15 un_m50_54_1993
rename var16 un_m55_64_1993
rename var17 un_m65plus_1993
gen un_w12_17_1993=var18+var19
gen un_w18_24_1993=var20+var21
rename  var22 un_w25_34_1993
gen  un_w35_49_1993=var23+var24
rename var25 un_w50_54_1993
rename var26 un_w55_64_1993
rename var27 un_w65plus_1993
rename var28 workingwomen_1993
rename var29 black_1993
rename var31 spanish_1993
rename var32 hhnoadults_1993
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1993 tvhh_1993 un_c2_5_1993 un_c6_11_1993 un_m12_17_1993 un_m18_24_1993 un_m25_34_1993 un_m35_49_1993 un_m50_54_1993 un_m55_64_1993 un_m65plus_1993 un_w12_17_1993 un_w18_24_1993 un_w25_34_1993 un_w35_49_1993 un_w50_54_1993 un_w55_64_1993 un_w65plus_1993 workingwomen_1993 black_1993 spanish_1993 hhnoadults_1993
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe93.dta", replace

clear
use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw94.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var32, replace
rename var1 dmacode
rename var3 totalhh_1994
rename var4 tvhh_1994 
rename var6 un_c2_5_1994
rename var7 un_c6_11_1994
gen un_m12_17_1994=var8+var9
gen un_m18_24_1994=var10+var11
rename  var12 un_m25_34_1994
gen  un_m35_49_1994=var13+var14
rename var15 un_m50_54_1994
rename var16 un_m55_64_1994
rename var17 un_m65plus_1994
gen un_w12_17_1994=var18+var19
gen un_w18_24_1994=var20+var21
rename  var22 un_w25_34_1994
gen  un_w35_49_1994=var23+var24
rename var25 un_w50_54_1994
rename var26 un_w55_64_1994
rename var27 un_w65plus_1994
rename var28 workingwomen_1994
rename var29 black_1994
rename var31 spanish_1994
rename var32 hhnoadults_1994
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1994 tvhh_1994 un_c2_5_1994 un_c6_11_1994 un_m12_17_1994 un_m18_24_1994 un_m25_34_1994 un_m35_49_1994 un_m50_54_1994 un_m55_64_1994 un_m65plus_1994 un_w12_17_1994 un_w18_24_1994 un_w25_34_1994 un_w35_49_1994 un_w50_54_1994 un_w55_64_1994 un_w65plus_1994 workingwomen_1994 black_1994 spanish_1994 hhnoadults_1994
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe94.dta", replace


clear
use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw95.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var32, replace
rename var1 dmacode
rename var3 totalhh_1995
rename var4 tvhh_1995 
rename var6 un_c2_5_1995
rename var7 un_c6_11_1995
gen un_m12_17_1995=var8+var9
gen un_m18_24_1995=var10+var11
rename  var12 un_m25_34_1995
gen  un_m35_49_1995=var13+var14
rename var15 un_m50_54_1995
rename var16 un_m55_64_1995
rename var17 un_m65plus_1995
gen un_w12_17_1995=var18+var19
gen un_w18_24_1995=var20+var21
rename  var22 un_w25_34_1995
gen  un_w35_49_1995=var23+var24
rename var25 un_w50_54_1995
rename var26 un_w55_64_1995
rename var27 un_w65plus_1995
rename var28 workingwomen_1995
rename var29 black_1995
rename var31 spanish_1995
rename var32 hhnoadults_1995
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1995 tvhh_1995 un_c2_5_1995 un_c6_11_1995 un_m12_17_1995 un_m18_24_1995 un_m25_34_1995 un_m35_49_1995 un_m50_54_1995 un_m55_64_1995 un_m65plus_1995 un_w12_17_1995 un_w18_24_1995 un_w25_34_1995 un_w35_49_1995 un_w50_54_1995 un_w55_64_1995 un_w65plus_1995 workingwomen_1995 black_1995 spanish_1995 hhnoadults_1995
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe95.dta", replace


clear
use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw97.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var32, replace
rename var1 dmacode
rename var3 totalhh_1997
rename var4 tvhh_1997 
rename var6 un_c2_5_1997
rename var7 un_c6_11_1997
gen un_m12_17_1997=var8+var9
gen un_m18_24_1997=var10+var11
rename  var12 un_m25_34_1997
gen  un_m35_49_1997=var13+var14
rename var15 un_m50_54_1997
rename var16 un_m55_64_1997
rename var17 un_m65plus_1997
gen un_w12_17_1997=var18+var19
gen un_w18_24_1997=var20+var21
rename  var22 un_w25_34_1997
gen  un_w35_49_1997=var23+var24
rename var25 un_w50_54_1997
rename var26 un_w55_64_1997
rename var27 un_w65plus_1997
rename var28 workingwomen_1997
rename var29 black_1997
rename var31 spanish_1997
rename var32 hhnoadults_1997
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1997 tvhh_1997 un_c2_5_1997 un_c6_11_1997 un_m12_17_1997 un_m18_24_1997 un_m25_34_1997 un_m35_49_1997 un_m50_54_1997 un_m55_64_1997 un_m65plus_1997 un_w12_17_1997 un_w18_24_1997 un_w25_34_1997 un_w35_49_1997 un_w50_54_1997 un_w55_64_1997 un_w65plus_1997 workingwomen_1997 black_1997 spanish_1997 hhnoadults_1997
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe97.dta", replace

clear
*1992-2000-2002-2003-2004-2005-2006*

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw92.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var30, replace
rename var1 dmacode
gen totalhh_1992=var3/100
gen tvhh_1992 =var4/10
rename var8 un_c2_5_1992
rename var9 un_c6_11_1992
gen un_m12_17_1992=var10+var11
gen un_m18_24_1992=var12+var13
rename  var14 un_m25_34_1992
gen  un_m35_49_1992=var15+var16
rename var17 un_m50_54_1992
rename var18 un_m55_64_1992
rename var19 un_m65plus_1992
gen un_w12_17_1992=var20+var21
gen un_w18_24_1992=var22+var23
rename  var24 un_w25_34_1992
gen  un_w35_49_1992=var25+var26
rename var27 un_w50_54_1992
rename var28 un_w55_64_1992
rename var29 un_w65plus_1992
rename var30 workingwomen_1992
gen black_1992=var5/10
gen spanish_1992=var6/10
*households with no adults is not available*
rename var7 housewithchildren_1992
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_1992 tvhh_1992 un_c2_5_1992 un_c6_11_1992 un_m12_17_1992 un_m18_24_1992 un_m25_34_1992 un_m35_49_1992 un_m50_54_1992 un_m55_64_1992 un_m65plus_1992 un_w12_17_1992 un_w18_24_1992 un_w25_34_1992 un_w35_49_1992 un_w50_54_1992 un_w55_64_1992 un_w65plus_1992 workingwomen_1992 black_1992 spanish_1992 housewithchildren_1992
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe92.dta", replace

clear

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw00.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var30, replace
rename var1 dmacode
gen totalhh_2000=var3/100
gen tvhh_2000 =var4/10
rename var8 un_c2_5_2000
rename var9 un_c6_11_2000
gen un_m12_17_2000=var10+var11
gen un_m18_24_2000=var12+var13
rename  var14 un_m25_34_2000
gen  un_m35_49_2000=var15+var16
rename var17 un_m50_54_2000
rename var18 un_m55_64_2000
rename var19 un_m65plus_2000
gen un_w12_17_2000=var20+var21
gen un_w18_24_2000=var22+var23
rename  var24 un_w25_34_2000
gen  un_w35_49_2000=var25+var26
rename var27 un_w50_54_2000
rename var28 un_w55_64_2000
rename var29 un_w65plus_2000
rename var30 workingwomen_2000
gen black_2000=var5/10
gen spanish_2000=var6/10
*households with no adults is not available*
rename var7 housewithchildren_2000
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_2000 tvhh_2000 un_c2_5_2000 un_c6_11_2000 un_m12_17_2000 un_m18_24_2000 un_m25_34_2000 un_m35_49_2000 un_m50_54_2000 un_m55_64_2000 un_m65plus_2000 un_w12_17_2000 un_w18_24_2000 un_w25_34_2000 un_w35_49_2000 un_w50_54_2000 un_w55_64_2000 un_w65plus_2000 workingwomen_2000 black_2000 spanish_2000 housewithchildren_2000
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe00.dta", replace

clear

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw02.dta"
drop if var2==""
drop if var2=="DMA Name"
rename var1 dmacode
split dmacode,p("")
drop dmacode dmacode2
rename dmacode1 dmacode 
destring dmacode, replace
destring var2-var30, replace

gen totalhh_2002=var3/100
gen tvhh_2002 =var4/10
rename var8 un_c2_5_2002
rename var9 un_c6_11_2002
gen un_m12_17_2002=var10+var11
gen un_m18_24_2002=var12+var13
rename  var14 un_m25_34_2002
gen  un_m35_49_2002=var15+var16
rename var17 un_m50_54_2002
rename var18 un_m55_64_2002
rename var19 un_m65plus_2002
gen un_w12_17_2002=var20+var21
gen un_w18_24_2002=var22+var23
rename  var24 un_w25_34_2002
gen  un_w35_49_2002=var25+var26
rename var27 un_w50_54_2002
rename var28 un_w55_64_2002
rename var29 un_w65plus_2002
rename var30 workingwomen_2002
gen black_2002=var5/10
gen spanish_2002=var6/10
*households with no adults is not available*
rename var7 housewithchildren_2002
gen dmaname=upper(var2)

keep dmaname dmacode totalhh_2002 tvhh_2002 un_c2_5_2002 un_c6_11_2002 un_m12_17_2002 un_m18_24_2002 un_m25_34_2002 un_m35_49_2002 un_m50_54_2002 un_m55_64_2002 un_m65plus_2002 un_w12_17_2002 un_w18_24_2002 un_w25_34_2002 un_w35_49_2002 un_w50_54_2002 un_w55_64_2002 un_w65plus_2002 workingwomen_2002 black_2002 spanish_2002 housewithchildren_2002
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe02.dta", replace

clear

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw03.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var30, replace
rename var1 dmacode
split dmacode,p("")
drop dmacode dmacode2
rename dmacode1 dmacode 
destring dmacode, replace

gen totalhh_2003=var3/100
gen tvhh_2003 =var4/10
rename var8 un_c2_5_2003
rename var9 un_c6_11_2003
gen un_m12_17_2003=var10+var11
gen un_m18_24_2003=var12+var13
rename  var14 un_m25_34_2003
gen  un_m35_49_2003=var15+var16
rename var17 un_m50_54_2003
rename var18 un_m55_64_2003
rename var19 un_m65plus_2003
gen un_w12_17_2003=var20+var21
gen un_w18_24_2003=var22+var23
rename  var24 un_w25_34_2003
gen  un_w35_49_2003=var25+var26
rename var27 un_w50_54_2003
rename var28 un_w55_64_2003
rename var29 un_w65plus_2003
rename var30 workingwomen_2003
gen black_2003=var5/10
gen spanish_2003=var6/10
*households with no adults is not available*
rename var7 housewithchildren_2003
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_2003 tvhh_2003 un_c2_5_2003 un_c6_11_2003 un_m12_17_2003 un_m18_24_2003 un_m25_34_2003 un_m35_49_2003 un_m50_54_2003 un_m55_64_2003 un_m65plus_2003 un_w12_17_2003 un_w18_24_2003 un_w25_34_2003 un_w35_49_2003 un_w50_54_2003 un_w55_64_2003 un_w65plus_2003 workingwomen_2003 black_2003 spanish_2003 housewithchildren_2003
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe03.dta", replace

clear

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw04.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var30, replace
rename var1 dmacode
gen totalhh_2004=var3/100
gen tvhh_2004 =var4/10
rename var8 un_c2_5_2004
rename var9 un_c6_11_2004
gen un_m12_17_2004=var10+var11
gen un_m18_24_2004=var12+var13
rename  var14 un_m25_34_2004
gen  un_m35_49_2004=var15+var16
rename var17 un_m50_54_2004
rename var18 un_m55_64_2004
rename var19 un_m65plus_2004
gen un_w12_17_2004=var20+var21
gen un_w18_24_2004=var22+var23
rename  var24 un_w25_34_2004
gen  un_w35_49_2004=var25+var26
rename var27 un_w50_54_2004
rename var28 un_w55_64_2004
rename var29 un_w65plus_2004
rename var30 workingwomen_2004
gen black_2004=var5/10
gen spanish_2004=var6/10
*households with no adults is not available*
rename var7 housewithchildren_2004
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_2004 tvhh_2004 un_c2_5_2004 un_c6_11_2004 un_m12_17_2004 un_m18_24_2004 un_m25_34_2004 un_m35_49_2004 un_m50_54_2004 un_m55_64_2004 un_m65plus_2004 un_w12_17_2004 un_w18_24_2004 un_w25_34_2004 un_w35_49_2004 un_w50_54_2004 un_w55_64_2004 un_w65plus_2004 workingwomen_2004 black_2004 spanish_2004 housewithchildren_2004
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe04.dta", replace

clear

use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw05.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var30, replace
rename var1 dmacode
gen totalhh_2005=var3/100
gen tvhh_2005 =var4/10
rename var8 un_c2_5_2005
rename var9 un_c6_11_2005
gen un_m12_17_2005=var10+var11
gen un_m18_24_2005=var12+var13
rename  var14 un_m25_34_2005
gen  un_m35_49_2005=var15+var16
rename var17 un_m50_54_2005
rename var18 un_m55_64_2005
rename var19 un_m65plus_2005
gen un_w12_17_2005=var20+var21
gen un_w18_24_2005=var22+var23
rename  var24 un_w25_34_2005
gen  un_w35_49_2005=var25+var26
rename var27 un_w50_54_2005
rename var28 un_w55_64_2005
rename var29 un_w65plus_2005
rename var30 workingwomen_2005
gen black_2005=var5/10
gen spanish_2005=var6/10
*households with no adults is not available*
rename var7 housewithchildren_2005
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_2005 tvhh_2005 un_c2_5_2005 un_c6_11_2005 un_m12_17_2005 un_m18_24_2005 un_m25_34_2005 un_m35_49_2005 un_m50_54_2005 un_m55_64_2005 un_m65plus_2005 un_w12_17_2005 un_w18_24_2005 un_w25_34_2005 un_w35_49_2005 un_w50_54_2005 un_w55_64_2005 un_w65plus_2005 workingwomen_2005 black_2005 spanish_2005 housewithchildren_2005
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe05.dta", replace

clear


use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universeraw06.dta"
drop if var2==""
drop if var2=="DMA Name"
destring var1-var30, replace
rename var1 dmacode
gen totalhh_2006=var3/100
gen tvhh_2006 =var4/10
rename var8 un_c2_5_2006
rename var9 un_c6_11_2006
gen un_m12_17_2006=var10+var11
gen un_m18_24_2006=var12+var13
rename  var14 un_m25_34_2006
gen  un_m35_49_2006=var15+var16
rename var17 un_m50_54_2006
rename var18 un_m55_64_2006
rename var19 un_m65plus_2006
gen un_w12_17_2006=var20+var21
gen un_w18_24_2006=var22+var23
rename  var24 un_w25_34_2006
gen  un_w35_49_2006=var25+var26
rename var27 un_w50_54_2006
rename var28 un_w55_64_2006
rename var29 un_w65plus_2006
rename var30 workingwomen_2006
gen black_2006=var5/10
gen spanish_2006=var6/10
*households with no adults is not available*
rename var7 housewithchildren_2006
gen dmaname=upper(var2)
keep dmaname dmacode totalhh_2006 tvhh_2006 un_c2_5_2006 un_c6_11_2006 un_m12_17_2006 un_m18_24_2006 un_m25_34_2006 un_m35_49_2006 un_m50_54_2006 un_m55_64_2006 un_m65plus_2006 un_w12_17_2006 un_w18_24_2006 un_w25_34_2006 un_w35_49_2006 un_w50_54_2006 un_w55_64_2006 un_w65plus_2006 workingwomen_2006 black_2006 spanish_2006 housewithchildren_2006
save "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe06.dta", replace

clear



use "C:\Documents and Settings\axz051000\My Documents\guale2009\stan\createratingdatanew\universe90.dta"

joinby dmacode using universe91, unmatched(both)
tab _merge
tab dmacode dmaname if _merge==1
drop _merge
joinby dmacode using universe92, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe91, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe92, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe93, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe94, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe95, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe96, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe97, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe98, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe99, unmatched(both)
tab dmacode dmaname if _merge~=3

tab _merge
drop _merge
joinby dmacode using universe00, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe01, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe02, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3
drop _merge

joinby dmacode using universe03, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe04, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe05, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge
joinby dmacode using universe06, unmatched(both)
tab _merge
tab dmacode dmaname if _merge~=3

drop _merge

gen marketid=dmacode-400

joinby marketid using rating19902006finalnew.dta, unmatched(both)
tab _merge, nolabel


save ratinganduniverse19902006final.dta, replace

destring, replace

forvalues i = 1990/2006 {
		gen pobdma_`i'=un_c2_5_`i'+un_c6_11_`i'+un_m12_17_`i'+un_m18_24_`i'+un_m25_34_`i'+un_m35_49_`i'+un_m50_54_`i'+un_m55_64_`i'+un_m65plus_`i'+un_w12_17_`i'+un_w18_24_`i'+un_w25_34_`i'+un_w35_49_`i'+un_w50_54_`i'+un_w55_64_`i'+un_w65plus_`i'
}

forvalues i = 1990/2006 {
		gen ratingdma_`i'=(ra_c2_5_`i'*un_c2_5_`i'+ra_c6_11_`i'*un_c6_11_`i'+ra_m12_17_`i'*un_m12_17_`i'+ra_m18_24_`i'*un_m18_24_`i'+ra_m25_34_`i'*un_m25_34_`i'+ra_m35_49_`i'*un_m35_49_`i'+ra_m50_54_`i'*un_m50_54_`i'+ra_m55_64_`i'*un_m55_64_`i'+ra_m65plus_`i'*un_m65plus_`i'+ra_w12_17_`i'*un_w12_17_`i'+ra_w18_24_`i'*un_w18_24_`i'+ra_w25_34_`i'*un_w25_34_`i'+ra_w35_49_`i'*un_w35_49_`i'+ra_w50_54_`i'*un_w50_54_`i'+ra_w55_64_`i'*un_w55_64_`i'+ra_w65plus_`i'*un_w65plus_`i')/pobdma_`i'
}


forvalues i = 1990/2006 {
		gen pobdma6_34_`i'=un_c6_11_`i'+un_m12_17_`i'+un_m18_24_`i'+un_m25_34_`i'+un_w12_17_`i'+un_w18_24_`i'+un_w25_34_`i'
}

forvalues i = 1990/2006 {
		gen ratingdma6_34_`i'=(ra_c6_11_`i'*un_c6_11_`i'+ra_m12_17_`i'*un_m12_17_`i'+ra_m18_24_`i'*un_m18_24_`i'+ra_m25_34_`i'*un_m25_34_`i'+ra_w12_17_`i'*un_w12_17_`i'+ra_w18_24_`i'*un_w18_24_`i'+ra_w25_34_`i'*un_w25_34_`i')/pobdma6_34_`i'
}

forvalues i = 1990/2006 {
	gen pobdma35_54_`i'=un_m35_49_`i'+un_m50_54_`i'+un_w35_49_`i'+un_w50_54_`i'
}

forvalues i = 1990/2006 {
		gen ratingdma35_54_`i'=(ra_m35_49_`i'*un_m35_49_`i'+ra_m50_54_`i'*un_m50_54_`i'+ra_w35_49_`i'*un_w35_49_`i'+ra_w50_54_`i'*un_w50_54_`i')/pobdma35_54_`i'
}


forvalues i = 1990/2006 {
		gen pobdma55plus`i'=un_m55_64_`i'+un_m65plus_`i'+un_w55_64_`i'+un_w65plus_`i'
}

forvalues i = 1990/2006 {
		gen ratingdma55plus`i'=(ra_m55_64_`i'*un_m55_64_`i'+ra_m65plus_`i'*un_m65plus_`i'+ra_w55_64_`i'*un_w55_64_`i'+ra_w65plus_`i'*un_w65plus_`i')/pobdma55plus`i'
}

drop _merge

save ratinganduniverse19902006final.dta, replace
